<?php

namespace app\api\controller;

use app\common\controller\Api;
use app\common\model\CashCode;
use think\Db;
use think\Request;
/**
 * 关键词搜索
 */
class SearchResult extends Api
{

    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页  http://fadmin.uni.com/api/search
     * 
     */
    public function index()
    {	
	
		$this->product_attr_color('red',5);
        $this->success('请求成功');
    }
	
	
	
	
	
	
	
	
	

	
	
	
		
	
	
	

	
	
	
	
	//查询是否存在指定文章需要内容匹配【在匹配标题无结果的时候才执行】
	//规则1：搜索客户习惯购买产品 是否在结果内 在则排序最高
	//规则2：搜索结果中 进行产品销售 排序靠前
	//规则3：
	
	private function q_product($keyword,$item){
		
		// 1. sku 匹配  无结果 则进行 产品名称匹配
		$skulist = $this->product_sku($keyword,$item);
		if($skulist != '[]'){
			
			return $skulist;
			
			
		}
		// 2. sku 无结果 进行 产品名称 匹配
		$productlist = $this->product_name($keyword,$item);
		if($productlist != '[]'){
			
			
			return  $productlist;
			
		}
		
		// 3. 产品名称 无结果  进行 产品feature 颜色 匹配
		$featurelist =  $this->product_feature_color($keyword,$item);
		if($featurelist != '[]'){
			
			
			return  $featurelist;
			
		}
			
	
		// 4. 产品feature 无结果  进行 产品组合color 匹配
		$attrlist =  $this->product_attr_color($keyword,$item);
		
		if($attrlist != '[]'){
			
			
			return  $attrlist;
			
		}
		
		$this->q_product_similar();
		
		return  '[]';
	
	}
	
	//产品组合 sku 精准匹配 
	// 规则1  执行 sku匹配
	private function  product_sku($keyword,$item){
		//模糊匹配 fa_keyword_hot
		$sql = "select a.id_product,a.reference,b.`name`,  CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
where   a.active = 1 and  a.reference like '%".$keyword."%' limit 0,$item" ;
		
		$res = Db::query($sql);


		return json_encode($res) ;
	
	}
	
	
	//产品名称 匹配
	// 规则1 sku 无匹配结果后 执行 产品名称匹配
	private function  product_name($keyword,$item){
		$id_customer = $this->request->post("id_customer");
		$sql =  "select a.id_product,a.reference,b.`name`,  CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
where   a.active = 1 and    b.name like '%".$keyword."%' ";
		
		//如果是新客户 则执行销量排序   --老客户则执行购买习惯排序
		if($this->isOld(139469)){
			//老客户
			
			//先做 销量排序
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= " limit 0,$item" ;
				
				$res = Db::query($sql);

			
				
			}else{
				//存在用户购买 习惯排序
				if( $this->product_sort_habit(139469) == '' ){
					
					//不存在购买习惯 数据 默认销售数量排序
					$sql .= " ORDER BY   field(a.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
					$res = Db::query($sql);
		
					
					
				}else{
					
					//存在 则先对 销售数量排序 在此基础 再对购买习惯排序
					$sql = " select  * from  (
					select a.id_product,a.reference,b.`name`,  CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
where   a.active = 1 and    b.name like '%".$keyword."%'  ORDER BY   field(a.id_product,". $this->product_sort_sales().")   
desc   ) myaa 
ORDER BY	field(myaa.id_product,".$this->product_sort_habit(139469).") DESC

limit 0,$item " ;

					$res = Db::query($sql);
	
					
				}
				
				
			}
			
			
		}else{
			//新客户 未注册用户
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= "  limit 0,$item" ;
				
				$res = Db::query($sql);

			
				
			}else{
				//存在产品 销售排行 id
				
				$sql .= "  ORDER BY   field(a.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
				$res = Db::query($sql);
	
			
				
			}
			
			
		}
		
		
		$res = $this->product_filter_out($res);//过滤结果
		
		return json_encode($res) ;
		
	}
	

	
	//产品组合 feature 特征匹配
	//规则1 sku ，name 无匹配结果 才开始此处匹配
	private function  product_feature_color($keyword,$item){
		
		$id_customer = $this->request->post("id_customer");
		
		$sql = "select b.id_product,c.reference ,d.`name` ,
				CONCAT(f.link_rewrite,'/',d.id_product,'-',d.link_rewrite) as url

				from  ps_feature_value_lang  a 
				LEFT JOIN ps_feature_product  b  on  a.id_feature_value = b.id_feature_value
				LEFT JOIN ps_product  c  on  b.id_product = c.id_product and c.active =1
				LEFT JOIN ps_product_lang  d  on  c.id_product = d.id_product  and d.id_lang=1 and d.id_shop=1
				LEFT JOIN ps_category_lang  f  on c.id_category_default =f.id_category  and  f.id_shop=1
				where  a.`value`  like  '%".$keyword."%'  and  b.id_product  is not null  and c.reference  is not null
				 " ;
		
		if($this->isOld(139469)){
			
			if( $this->product_sort_habit(139469) == '' ){
					
					//不存在购买习惯 数据 默认销售数量排序
					$sql .= " ORDER BY   field(b.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
					$res = Db::query($sql);
		
				
					
					
			}else{
				
				//存在 则先对 销售数量排序 在此基础 再对购买习惯排序
					$sql = " select  * from  ( ".$sql."  ORDER BY   field(b.id_product,". $this->product_sort_sales().")   
desc   ) myaa 
ORDER BY	field(myaa.id_product,".$this->product_sort_habit(139469).") DESC

limit 0,$item " ;
					
					$res = Db::query($sql);

			
				
				
				
			}
			 
			
		}else{
			
			//新客户 未注册用户
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= "limit 0,$item";
				$res = Db::query($sql);

		
				
			}else{
				//存在产品 销售排行 id
				
				$sql .= "  ORDER BY   field(b.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
				$res = Db::query($sql);
	
				
				
			}
			
		}
		
		
		$res = $this->product_filter_out($res);//过滤结果
		return json_encode($res) ;
		
	}
	
	
	//产品组合 attr 组合颜色匹配
	//规则1 sku ，name ，feature 无匹配结果 才开始此处匹配
	private function  product_attr_color($keyword,$item){
		
		$id_customer = $this->request->post("id_customer");
		
		$sql = "select mya.*  from  (
select  a.`name`,c.id_product from  ps_attribute_lang  a 
LEFT JOIN  ps_product_attribute_combination   b  on a.id_attribute =b.id_attribute 
LEFT JOIN  ps_product_attribute  c  on b.id_product_attribute=c.id_product_attribute 
where  a.`name`  like  '%" .$keyword."%'  and  c.id_product  is not  null )mya  
LEFT JOIN  ps_product dd  on  mya.id_product = dd.id_product  
where  dd.active =1 
GROUP BY  mya.id_product
 " ;

		if($this->isOld(139469)){
			
			if( $this->product_sort_habit(139469) == '' ){
					
					//不存在购买习惯 数据 默认销售数量排序
					$sql .= " ORDER BY   field(mya.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
					$res = Db::query($sql);
		
				
					
					
			}else{
				
				//存在 则先对 销售数量排序 在此基础 再对购买习惯排序
					$sql = " select  * from  ( ".$sql."  ORDER BY   field(mya.id_product,". $this->product_sort_sales().")   
desc   ) myaa 
ORDER BY	field(myaa.id_product,".$this->product_sort_habit(139469).") DESC

limit 0,$item " ;	
		
					$res = Db::query($sql);
	

			}
			 
			
		}else{
			
			//新客户 未注册用户
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= "limit 0,$item";
				$res = Db::query($sql);

				
			}else{
				//存在产品 销售排行 id
				
				$sql .= "  ORDER BY   field(mya.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
				$res = Db::query($sql);

				
			}
			
		}
		
		$res = $this->product_filter_out($res);//过滤结果
		
	    return json_encode($res) ;
	}
	
	
	// 在sku  name  attr  feature 均无结果 则进行相似匹配 推荐词	
	//相识度匹配 75%以上
	//人工指定 相似度匹配词汇	
	private function q_product_similar(){
		
		
		
	}
	
	
	//产品结果 筛选 根据客户购买产品的数量多少 做排序 新用户不做此排序
	//规则1. 先判断结果中产品 用户是否购买过 
	//规则2. 结果中存在购买结果 排序靠前
	//规则3. 结果中存在多个购买产品结果 再对购买产品数量做排序 靠前
	private function  product_sort_habit($id_customer){
		
		$sql= "select * from  (
select  a.id_order,a.id_customer,b.product_id,sum(b.product_quantity) as num  from  ps_orders a  
LEFT JOIN  ps_order_detail  b  on a.id_order=b.id_order
where  a.id_customer  = $id_customer  and b.product_id  is not null
GROUP BY  b.product_id  ) mya 
ORDER BY  mya.num  desc ";
		$res = Db::query($sql);
		
		$str =  '';
		foreach($res as $a){
			
			$str .= $a['product_id'] .',';
	
		}
		
		$str = rtrim($str,',');

		return  $str ;
	}
	//产品结果 筛选  对 用户购买习惯结果 做二次筛选 
	//规则1. 依据结果产品 的销量做排序
	//规则2. 销量时间--半年内的销量
	//规则3：如果统统为0 则不做排序
	//返回 产品 销售 排序 id
	private function  product_sort_sales(){
		
		$sql= 'select mya.product_id from  (
select  b.product_id,sum(b.product_quantity) as num  from  ps_orders a  
LEFT JOIN  ps_order_detail  b  on a.id_order=b.id_order
where b.product_id  is not null  and  a.date_add >= DATE_SUB(now(),INTERVAL 180 DAY)  
GROUP BY  b.product_id  ) mya 
ORDER BY  mya.num  desc 
#limit 0,100
';		
		$res = Db::query($sql);
		
		$str =  '';
		foreach($res as $a){
			
			$str .= $a['product_id'] .',';
	
		}
		
		$str = rtrim($str,',');

		return  $str ;
		
	}
	
	
	//判断是否 登陆  判断是否新老客户
	private function  isOld($id_customer){
		
		$sql = "select id_order from  ps_orders where  id_customer = $id_customer limit 0,1";
		$res = Db::query($sql);
		return $res; 
	}
	
	
	
	//结果中 过滤掉 禁用分类下的产品 
	private function  product_filter_out($res){
			
			$sql = "select GROUP_CONCAT(b.id_product) as productlist from  fa_keyword_category  a
LEFT JOIN  ps_category_product  b  on a.id_category= b.id_category

where  a.`status` = '正常'";

			$filter = Db::query($sql);
	
			if($filter[0]['productlist']){
				
				$i = 0 ;
				foreach($res  as $a){
					
					if( strpos('x'.$filter[0]['productlist'].',41770' , $a['id_product'].'' )){
						
						 unset($res[$i]);
						
					}
					$i++;	
				}
				
				return  $res ;
			
				
			}else{
				
				return $res ;
			}
	
		
	}
	
	
/* 	//拆分词库   产品名称 , 产品sku  , 产品feature, 产品组合颜色
	public function  split_keywords(){
		
		//空格拆分
		
	} */
	
	//搜索结果页面 
	public function  myresult(){
		
		
		
		if($this->request->isPost()){
			
			Request::instance()->filter(['strip_tags','htmlspecialchars','trim']);
			$keyword = $this->request->post("keyword");
			$category = $this->request->post("category");
			$sort = $this->request->post("sort");
			
			echo '<pre>';
			var_dump($_POST);
			echo '</pre>';
			die('debug!!!');
			
			
		}
		
	}
	//新品排序  默认全部都有   默认0  则是全部 根据结果中的分类进行排序
	private function  category(){
		
		
	}
	
	
	//新品排序  默认排序
	private function  sort_new(){
		
		$sql
		
	}
	//
	private function  sort_price(){
		
		
	}
	private  function  sort_sales(){
		
		
	}
}
